-- Descriere:
--
-- Contine un packet care trebuie sa creeze toate entitatile (tabele, 
-- triggere, etc..) necesare packetului 'eiga'
--
-- Neterminat!

CREATE OR REPLACE PACKAGE eiga_install AS
  PROCEDURE users_create;
--  PROCEDURE users_truncate;
END eiga_install;
/

CREATE OR REPLACE PACKAGE BODY eiga_install AS
PROCEDURE users_create IS
BEGIN
  DROP SEQUENCE seq_user_id;
  CREATE SEQUENCE seq_user_id
    START WITH 1 
    INCREMENT BY 1 
    NOMAXVALUE; 
END users_create;
END eiga_install;
/

CREATE OR REPLACE PACKAGE BODY eiga_install AS
PROCEDURE users_create IS
BEGIN
  -- cream tabelul
  DROP TABLE users;
  CREATE TABLE users(
    user_id NUMBER(4),
    user_name VARCHAR2(50),
    user_password VARCHAR2(50),
    CONSTRAINT users_user_id_pk PRIMARY KEY (user_id));
END users_create;

PROCEDURE users_truncate IS
BEGIN
  -- cream secventa
  DROP SEQUENCE seq_user_id;
  CREATE SEQUENCE seq_user_id
    START WITH 1 
    INCREMENT BY 1 
    NOMAXVALUE; 
  
  -- cream triggerul
  CREATE OR REPLACE TRIGGER trg_user_id
  BEFORE INSERT ON users
  FOR EACH ROW
  BEGIN
    SELECT seq_user_id.nextval INTO :new.user_id FROM dual;
  END;
  
  -- reintroducem datele
  TRUNCATE TABLE users;
  INSERT INTO users (user_id, user_name, user_password)
    VALUES (1, 'radu', 'cevagreu');
    
  INSERT INTO users (user_id, user_name, user_password)
    VALUES (2, 'ghita', 'portita');
END users_truncate;

END eiga_install;
/
